﻿using System;
using System.IO;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Collections.Generic;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using com.lowagie.text;
using com.lowagie.text.rtf;
using GDI = System.Drawing;
using CMS.Core.Domain;
using CMS.Data;

namespace CMS.Web.Reports
{
    public partial class LookupReportView : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {            
            // Get params
            String type = Request["type"];
            String mod = Request["mod"];
            String txt1 = ProjectBase.Utils.CRequest.GetQueryUri("txt1", ""); //Request["txt1"];
            String txt2 = ProjectBase.Utils.CRequest.GetQueryUri("txt2", ""); //Request["txt2"];
            String txt3 = ProjectBase.Utils.CRequest.GetQueryUri("txt3", ""); //Request["txt3"];
            String txt4 = ProjectBase.Utils.CRequest.GetQueryUri("txt4", ""); //Request["txt4"];

            String txtNgay1 = Request["txtNgay1"];
            String txtNgay2 = Request["txtNgay2"];

            String txtMinSoThuLy = Request["txtMinSoThuLy"];
            String txtMaxSoThuLy = Request["txtMaxSoThuLy"];

            String cmbPB = ProjectBase.Utils.CRequest.GetQueryUri("cmbPB", ""); //Request["cmbPB"];
            String cmbNguon = ProjectBase.Utils.CRequest.GetQueryUri("cmbNguon", "-1"); //Request["cmbNguon"];
            String cmbTTXL = ProjectBase.Utils.CRequest.GetQueryUri("cmbTTXL", "0"); //Request["cmbTTXL"];
            String cmbLoaiDon = ProjectBase.Utils.CRequest.GetQueryUri("cmbLoaiDon", "0"); //Request["cmbLoaiDon"];
            String cmbDiaBan = ProjectBase.Utils.CRequest.GetQueryUri("cmbDiaBan", "0"); //Request["cmbDiaBan"];
            String cmbLinhVuc = ProjectBase.Utils.CRequest.GetQueryUri("cmbLinhVuc", "0");

            int total;
            IList list;
            IList totals;

            switch (mod)
            {
                case "TH":
                    totals = NHibernateDaoFactory.Instance.GetDonThuDao().TotalSearchDonThus(type, txt1, txt2, txtNgay1, txtNgay2,
                cmbPB, Convert.ToInt32(cmbLoaiDon), cmbDiaBan, Convert.ToInt32(cmbNguon), Convert.ToInt32(cmbTTXL));

                    list = NHibernateDaoFactory.Instance.GetDonThuDao().SearchDonThus(type, txt1, txt2, txtNgay1, txtNgay2,
               cmbPB, Convert.ToInt32(cmbLoaiDon), cmbDiaBan, Convert.ToInt32(cmbNguon), Convert.ToInt32(cmbTTXL), 0, (int)totals[0]);

                    XuatExcel(list, "BÁO CÁO ĐƠN THƯ TỔNG HỢP");
                    break;

                case "TNHAN":
                    totals = NHibernateDaoFactory.Instance.GetDonThuDao().TotalSearchDonThus(type, txt1, txt2, txtNgay1, txtNgay2,
               cmbPB, Convert.ToInt32(cmbLoaiDon), cmbDiaBan, Convert.ToInt32(cmbNguon), Convert.ToInt32(cmbTTXL));

                    list = NHibernateDaoFactory.Instance.GetDonThuDao().SearchDonThus(type, txt1, txt2, txtNgay1, txtNgay2,
               cmbPB, Convert.ToInt32(cmbLoaiDon), cmbDiaBan, Convert.ToInt32(cmbNguon), Convert.ToInt32(cmbTTXL), 0, (int)totals[0]);

                    XuatExcel(list, "BÁO CÁO ĐƠN THƯ THEO NGUỒN TIẾP NHẬN");
                    break;
                case "DV":
                    totals = NHibernateDaoFactory.Instance.GetDonThuDao().TotalSearchDonThuCQs(type, txt1, txt2, cmbPB, txtNgay1, txtNgay2,
                Convert.ToInt32(cmbLoaiDon), cmbDiaBan, Convert.ToInt32(cmbTTXL));

                    list = NHibernateDaoFactory.Instance.GetDonThuDao().SearchDonThuCQs(type, txt1, txt2, cmbPB, txtNgay1, txtNgay2,
               Convert.ToInt32(cmbLoaiDon), cmbDiaBan, Convert.ToInt32(cmbTTXL), 0, (int)totals[0]);

                    XuatExcel(list, "BÁO CÁO ĐƠN THƯ THEO CƠ QUAN GIẢI QUYẾT");
                    break;
                case "CGQ":
                    totals = NHibernateDaoFactory.Instance.GetDonThuDao().TotalSearchDonThuChuaXLs(type, txt1, txt2, txtNgay1, txtNgay2,
                Convert.ToInt32(cmbLoaiDon), cmbDiaBan, Convert.ToInt32(cmbTTXL));

                    list = NHibernateDaoFactory.Instance.GetDonThuDao().SearchDonThuChuaXLs(type, txt1, txt2, txtNgay1, txtNgay2,
            Convert.ToInt32(cmbLoaiDon), cmbDiaBan, Convert.ToInt32(cmbTTXL), 0, (int)totals[0]);

                    XuatExcel(list, "BÁO CÁO ĐƠN THƯ CHƯA GIẢI QUYẾT");
                    break;
                case "DGQ":
                    totals = NHibernateDaoFactory.Instance.GetDonThuDao().TotalSearchDonThuCQs(type, txt1, txt2, cmbPB, txtNgay1, txtNgay2,
                Convert.ToInt32(cmbLoaiDon), cmbDiaBan, Convert.ToInt32(cmbTTXL));

                    list = NHibernateDaoFactory.Instance.GetDonThuDao().SearchDonThuCQs(type, txt1, txt2, cmbPB,txtNgay1, txtNgay2,               
            Convert.ToInt32(cmbLoaiDon), cmbDiaBan, Convert.ToInt32(cmbTTXL), 0, (int)totals[0]);

                    XuatExcel(list, "BÁO CÁO ĐƠN THƯ ĐÃ GIẢI QUYẾT");
                    break;
                case "VLAI":
                    totals = NHibernateDaoFactory.Instance.GetDonThuDao().TotalSearchDonThuVangLais(type, txt1, txt2, txtNgay1, txtNgay2,
                cmbPB, Convert.ToInt32(cmbLoaiDon), cmbDiaBan, Convert.ToInt32(cmbTTXL));

                    list = NHibernateDaoFactory.Instance.GetDonThuDao().SearchDonThuVangLais(type, txt1, txt2, txtNgay1, txtNgay2,
               cmbPB, Convert.ToInt32(cmbLoaiDon), cmbDiaBan, Convert.ToInt32(cmbTTXL), 0, (int)totals[0]);

                    XuatExcel(list, "BÁO CÁO ĐƠN THƯ VÃNG LAI");
                    break;

                default:
                    total = NHibernateDaoFactory.Instance.GetDonThuDao().TotalSearchDonThus(type, txt1, txt2, txt3,
                    txt4, txtNgay1, txtNgay2, Convert.ToInt32(txtMinSoThuLy), Convert.ToInt32(txtMinSoThuLy),
                    Convert.ToInt32(cmbLinhVuc), Convert.ToInt32(cmbLoaiDon), cmbDiaBan);

                    list = NHibernateDaoFactory.Instance.GetDonThuDao().SearchDonThus(type, txt1, txt2, txt3,
                            txt4, txtNgay1, txtNgay2, Convert.ToInt32(txtMinSoThuLy), Convert.ToInt32(txtMinSoThuLy),
                            Convert.ToInt32(cmbLinhVuc), Convert.ToInt32(cmbLoaiDon), cmbDiaBan, 0, total);
                    XuatExcelTraCuu(list);
                    break;
            }
        }

        private void XuatExcelTraCuu(IList results)
        {
            string copyFileName = "";
            int numRowStart = 3;
            Microsoft.Office.Interop.Excel.Range range;
            Microsoft.Office.Interop.Excel.Application xlApp = null;
            Microsoft.Office.Interop.Excel.Workbook workbook = null;
            Microsoft.Office.Interop.Excel.Worksheet worksheet = null;
            //open app
            xlApp = new Microsoft.Office.Interop.Excel.Application();
            if (xlApp == null) return;

            copyFileName = Server.MapPath(null) + @"\Temp\BCTinhHinhDonThu.xls";

            //open workbook
            workbook = xlApp.Workbooks.Open(copyFileName, Type.Missing,
                                    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                                    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                                    Type.Missing, Type.Missing, Type.Missing);
            worksheet = workbook.Sheets[1] as Microsoft.Office.Interop.Excel.Worksheet;
            worksheet.Name = "BCTinhHinhDonThu";
            //set title
            worksheet.Cells[1, 1] = "BÁO CÁO TÌNH HÌNH TIẾP NHẬN VÀ XỬ LÝ ĐƠN THƯ";
            for (int i = 0; i < results.Count; i++)
            {
                object[] _object = (object[])results[i];
                numRowStart++;
                worksheet.Cells[numRowStart, 1] = (numRowStart - 3);
                worksheet.Cells[numRowStart, 2] = (_object[1] != null) ? _object[1] : "";
                worksheet.Cells[numRowStart, 3] = (_object[10] != null) ? _object[10] : "";
                worksheet.Cells[numRowStart, 4] = _object[2];
                worksheet.Cells[numRowStart, 5] = _object[3];
                worksheet.Cells[numRowStart, 6] = _object[4];
                worksheet.Cells[numRowStart, 7] = _object[5];
                worksheet.Cells[numRowStart, 8] = _object[12];
                worksheet.Cells[numRowStart, 9] = _object[6];
                worksheet.Cells[numRowStart, 10] = _object[7];
                worksheet.Cells[numRowStart, 11] = _object[11];
                worksheet.Cells[numRowStart, 12] = _object[8];
                worksheet.Cells[numRowStart, 13] = _object[9];
            }
            range = worksheet.get_Range("B4", "C" + numRowStart.ToString());
            range.NumberFormat = "dd/MM/yyyy";//"@";
            //set border
            range = worksheet.get_Range("A4", "M" + numRowStart.ToString());
            range.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
            range.Borders.Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;

            xlApp.Visible = true;
        }

        private void XuatExcel(IList results, string title)
        {
            ArrayList lstPos =null;
            string copyFileName = "";
            int numRowStart = 3, posTagsup=-1;
            string sTemp = "";

            Microsoft.Office.Interop.Excel.Range range;
            Microsoft.Office.Interop.Excel.Application xlApp = null;
            Microsoft.Office.Interop.Excel.Workbook workbook = null;
            Microsoft.Office.Interop.Excel.Worksheet worksheet = null;
            //open app
            xlApp = new Microsoft.Office.Interop.Excel.Application();
            if (xlApp == null) return;

            copyFileName = Server.MapPath(null) + @"\Temp\BCTinhHinhDonThu.xls";

            //open workbook
            workbook = xlApp.Workbooks.Open(copyFileName, Type.Missing,
                                    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                                    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                                    Type.Missing, Type.Missing, Type.Missing);
            worksheet = workbook.Sheets[1] as Microsoft.Office.Interop.Excel.Worksheet;
            worksheet.Name = "BCTinhHinhDonThu";

            //set title
            worksheet.Cells[1, 1] = title;// "BÁO CÁO ĐƠN THƯ TỔNG HỢP";

            for (int i = 0; i < results.Count; i++)
            {
                object[] _object = (object[])results[i];
                numRowStart++;
                lstPos = new ArrayList();
                worksheet.Cells[numRowStart, 1] = (numRowStart - 3);
                worksheet.Cells[numRowStart, 2] = (_object[1] != null) ? _object[1] : "";
                worksheet.Cells[numRowStart, 3] = (_object[10] != null) ? _object[10] : "";
                worksheet.Cells[numRowStart, 4] = _object[2];
                worksheet.Cells[numRowStart, 5] = _object[3];
                worksheet.Cells[numRowStart, 6] = _object[4];
                worksheet.Cells[numRowStart, 7] = _object[5];
                worksheet.Cells[numRowStart, 8] = _object[12];
                worksheet.Cells[numRowStart, 9] = _object[6];
                //sTemp = ProjectBase.Utils.CUtilities.RemoveTagP(_object[6].ToString());
                //int k = 0;
                //while (sTemp.IndexOf("</sup>", k) != -1)
                //{
                //    posTagsup = sTemp.IndexOf("</sup>", k);
                //    lstPos.Add(posTagsup);
                //    k = posTagsup + 5;
                //}
                //worksheet.Cells[numRowStart, 9] = ProjectBase.Utils.CUtilities.ConvertToHtml(sTemp);
                //k = 0;
                //if (lstPos.Count != 0)// set superscript
                //{
                //    range = worksheet.get_Range("I" + numRowStart.ToString(), "I" + numRowStart.ToString());
                //    foreach (int p in lstPos)
                //    {
                //        range.get_Characters(p - (5 + (k * 12)-k), 1).Font.Superscript = true;
                //        k++;
                //    }                    
                //}                            
                //range = worksheet.get_Range("I" + numRowStart.ToString(), "I" + numRowStart.ToString());
                //range.Charset 
                worksheet.Cells[numRowStart, 10] = _object[7];
                worksheet.Cells[numRowStart, 11] = _object[11];
                worksheet.Cells[numRowStart, 12] = _object[8];
                worksheet.Cells[numRowStart, 13] = _object[9];
            }
            range = worksheet.get_Range("B4", "C" + numRowStart.ToString());
            range.NumberFormat = "dd/MM/yyyy";//"@";
            //WrapText
            range = worksheet.get_Range("I4", "C" + numRowStart.ToString());
            range.Cells.WrapText = true;
            //range.Cells.Font.Subscript
            //range.get_Characters(2, 1).Font.Subscript = true;

            range = worksheet.get_Range("K4", "C" + numRowStart.ToString());
            range.Cells.WrapText = true;
            range = worksheet.get_Range("M4", "C" + numRowStart.ToString());
            range.Cells.WrapText = true;
            //set border
            range = worksheet.get_Range("A4", "M" + numRowStart.ToString());
            range.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
            range.Borders.Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;

            xlApp.Visible = true;
        }
    }
}
